package com.cyy.model

import cn.hutool.core.util.ReflectUtil
import com.jfinal.kit.Kv
import com.jfinal.plugin.activerecord.Config
import com.jfinal.plugin.activerecord.Db
import com.jfinal.plugin.activerecord.DbKit
import com.jfinal.plugin.activerecord.DbPro
import com.jfinal.plugin.activerecord.dialect.*
import com.jfinal.template.Directive
import com.jfinal.template.Engine
import com.jfinal.template.Env
import com.jfinal.template.expr.ast.ExprList
import com.jfinal.template.io.Writer
import java.sql.Connection
import java.util.*
import javax.sql.DataSource
import kotlin.collections.ArrayList

//import javax.sql.DataSource

object Constants {
    val pg = "postgresql"
    val mysql = "mysql"
    val sqlserver = "sqlserver"
    val oracle = "oracle"
    val sqlite = "sqlite"
    val h2 = "h2"
    val h2JdbcUrlPrefix = "jdbc:h2:file:"

    val getPgDbsSqlString = "select pg_database.datname from pg_database where datname not like 'template%' and datname not like 'Administrator' and datname not like 'postgres' order by datname;"
    val getPgTablesSqlString = "SELECT tablename FROM pg_tables WHERE schemaname='public';"

    val getMysqlDbsSqlString = "TABLE_CAT"
    fun getMysqlTablesSqlString(dbname: String): String = "select table_name from information_schema.TABLES where TABLE_SCHEMA='${dbname}'"

    val getSqliteTablesSqlString = "select name from sqlite_master where type='table' and name not like '%sqlite%' order by name"

    val getH2TableSqlString = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA ='PUBLIC'"

    // 演示函数有两个返回值的用法
    fun setJdbcUrlAndDriver(dbtype: String, host: String, port: String, dbname: String??): Pair<String, String> {
        var s1 = ""
        var s2 = ""
        var driver = ""
        when (dbtype) {
            "mysql" -> {
                s1 = "jdbc:${dbtype}://${host}:${port}/"
                driver = "com.mysql.cj.jdbc.Driver"
                s2 = "jdbc:mysql://${host}:${port}/${dbname}?characterEncoding=utf8&useInformationSchema=true&useSSL=false"
            }
            "mysql7" -> {
                s1 = "jdbc:${dbtype}://${host}:${port}/"
                driver = "com.mysql.jdbc.Driver"
                s2 = "jdbc:mysql://${host}:${port}/${dbname}?characterEncoding=utf8&useInformationSchema=true&useSSL=false"
            }
            "postgresql" -> {
                s1 = "jdbc:${dbtype}://${host}:${port}/"
                driver = "org.postgresql.Driver"
                s2 = "jdbc:postgresql://${host}:${port}/${dbname}?currentSchema=public,sys,app"
            }
            "oracle" -> {
                s1 = "jdbc:${dbtype}://${host}:${port}/"
                driver = "oracle.jdbc.driver.OracleDriver"
                s2 = "jdbc:oracle:thin:@${host}:${port}/${dbname}"
            }
            "sqlserver" -> {
                s1 = "jdbc:${dbtype}://${host}:${port}/"
                driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
                s2 = "jdbc:sqlserver://${host}:${port}/${dbname}"
            }
            "sqlite" -> {
                s1 = "jdbc:sqlite://${dbname}"
                driver = "org.sqlite.JDBC"
                s2 = s1
            }
            "h2" -> {
                s1 = "jdbc:sqlite://${dbname}"
                driver = "org.h2.Driver"
                s2 = s1
            }
            else -> {
                s1 = "jdbc:${dbtype}://${host}:${port}/"
                driver = ""
                s2 = "jdbc:${dbtype}://${host}:${port}/${dbname}"
            }
        }
        return Pair(s1, driver)
    }

    /**
     * 根据不同的数据库类型，设置不同的jdbcUrl字符串,用于测试是否能连接上数据库服务
     * @param:dbtype: String 数据库类型,可为空
     * @return 返回用于测试是否能连接上数据库服务的jdbcUrl字符串
     */
    fun setJdbcUrl1(dbtype: String, host: String, port: String, dbname: String??): String {
        return when (dbtype) {
            "mysql", "postgresql", "oracle", "sqlserver" -> "jdbc:${dbtype}://${host}:${port}/"
            "sqlite", "h2" -> "jdbc:${dbtype}://${dbname}"
            else -> "jdbc:${dbtype}://${host}:${port}/"
        }
    }

    /**
     * 根据不同的数据库类型及选择的数据库，设置不同的jdbcUrl字符串,用于连接具体的数据库
     * 如果为MySQL，url连接后面需要要添加useInformationSchema=true参数，否则表、列注释无法获取
     * 高版本驱动需要设置时区，进入命令行，按如下方式设置全局时区和当前会话时区为北京时间，即东8区
     * set global time_zone='+8:00';
     * set time_zone='+8:00';
     * flush privileges;
     * @param:dbtype: String 数据库类型,不能为空
     * @return 返回连接具体的数据库的jdbcUrl字符串
     */
    fun setJdbcUrl2(dbtype: String, host: String, port: String, dbname: String): String {
        return when (dbtype) {
            "mysql" -> "jdbc:mysql://${host}:${port}/${dbname}?characterEncoding=utf8&useInformationSchema=true&useSSL=false"
            "postgresql" -> "jdbc:postgresql://${host}:${port}/${dbname}?currentSchema=public,sys,app"
            "oracle" -> "jdbc:oracle:thin:@${host}:${port}/${dbname}"
            "sqlserver" -> "jdbc:sqlserver://${host}:${port}/${dbname}"
            "sqlite" -> "jdbc:sqlite://${dbname}"
            "h2" -> "jdbc:h2:file:${dbname};AUTO_SERVER=TRUE"
            else -> "jdbc:${dbtype}://${host}:${port}/${dbname}"
        }
    }
/* ====================================================
 *  使用建造者模式封闭不同数据库类型的属性
 */
    interface DbType {
        val dbDriver: String
        val jdbcUrlNoDb: String
        val jdbcUrlWithDb: String
        val dialect: Dialect
    }
    class Mysql:DbType{
        override val dialect: Dialect=MysqlDialect()
        override val dbDriver="com.mysql.cj.jdbc.Driver"
        override val jdbcUrlNoDb="com.mysql.cj.jdbc.Driver"
        override val jdbcUrlWithDb="com.mysql.cj.jdbc.Driver"

    }
    class Pg:DbType{
        override val dialect: Dialect=PostgreSqlDialect()
        override val dbDriver="org.postgresql.Driver"
        override val jdbcUrlNoDb="com.mysql.cj.jdbc.Driver"
        override val jdbcUrlWithDb="com.mysql.cj.jdbc.Driver"
    }
    class Sqlite:DbType{
        override val dialect: Dialect=Sqlite3Dialect()
        override val dbDriver="org.sqlite.JDBC"
        override val jdbcUrlNoDb="com.mysql.cj.jdbc.Driver"
        override val jdbcUrlWithDb="com.mysql.cj.jdbc.Driver"
    }
    class Parser(private val dbType: DbType) {
        val driver= dbType.dbDriver
        val dialect = dbType.dialect
    }
    val map0= mapOf(
            "mysql" to Mysql(),
            "sqlite" to Sqlite(),
            "postgresql" to Pg()
    )
    fun setDbDriver1(dbtype: String): String {
        return Parser(map0.get(dbtype)!!).driver
    }
    fun setDialect1(dbtype: String): Dialect {
        return Parser(map0.get(dbtype)!!).dialect
    }
// end 使用建造者模式封闭不同数据库类型的属性



    /**
     * 根据不同的数据库类型，设置不同的Driver字符串,用于测试是否能连接上数据库服务
     * @param:dbtype: String 数据库类型
     * @return 返回用于连接数据库服务的Driver字符串
     */
    fun setDbDriver(dbtype: String): String {
        return when (dbtype) {
            "mysql" -> "com.mysql.cj.jdbc.Driver"
            "mysql7" -> "com.mysql.jdbc.Driver"
            "postgresql" -> "org.postgresql.Driver"
            "oracle" -> "oracle.jdbc.driver.OracleDriver"
            "sqlserver" -> "com.microsoft.sqlserver.jdbc.SQLServerDriver"
            "sqlite" -> "org.sqlite.JDBC"
            "h2" -> "org.h2.Driver"
            "db2" -> "com.ibm.db2.jcc.DB2Driver"
            "hsqldb" -> "org.hsqldb.jdbcDriver"
            "derby" -> "org.apache.derby.jdbc.ClientDriver"
            "odbc" -> "sun.jdbc.odbc.JdbcOdbcDriver"
            else -> ""
        }
    }

    /**
     * 根据不同的数据库类型，设置不同的Dialect方言,用于连接数据库服务
     * @param:dbtype: String 数据库类型
     * @return 返回用于连接数据库服务的Dialect方言
     */
    fun setDialect(dbtype: String): Dialect {
        return when (dbtype) {
            "mysql", "mysql7" -> MysqlDialect()
            "postgresql" -> PostgreSqlDialect()
            "oracle" -> OracleDialect()
            "sqlserver" -> SqlServerDialect()
            "sqlite" -> Sqlite3Dialect()
            else -> AnsiSqlDialect()
        }
    }

    fun setTablesSql(dbtype: String, dbname: String): String {
        return when (dbtype) {
            "postgresql" -> Constants.getPgTablesSqlString
            "mysql" -> Constants.getMysqlTablesSqlString(dbname)
            "sqlite" -> Constants.getSqliteTablesSqlString
            "h2" -> Constants.getH2TableSqlString
            else -> ""
        }
    }

    fun closeDb(ds: DataSource) {
        try {
            ds.connection.close()
            println("db connection close success")
        } catch (e: Exception) {
            println("db connection close fail :${e}")
        }
    }

    /**
     *  DelKey 判断Kv是否包含key=name的项，如果存在，则删除
     * @author cyy
     * @param
     */
    fun DelKey(map: Kv, vararg name: String): Kv {
        arrayOf(name).forEach {
            if (map.containsKey(name)) {
                map.delete(name)
            }
        }
        return map
    }

    /**setActiveDb 当dataSource更改后,更新RecordDb
     * @param ds gmodel.dataSource.value
     * @param configName "config"
     */
    fun setRecordDb(configName: String, ds: DataSource,dialect: Dialect) : DbPro {
        val config= Config(configName, ds,dialect)
        try {
            DbKit.addConfig(config)
        }catch (e:Exception){
            println("""DbKit.addConfig(config) failed or the config is already exist""")
        }
        return Db.use(configName)
    }

    fun getSharedObject(engine: Engine):Collection<*>{
        val map= ReflectUtil.invoke(engine.getEngineConfig(), "getSharedObjectMap") as Map<String, Any>
        return map.values
    }
    fun getSharedObjectMap(engine: Engine):Map<String,Any>{
        val map= ReflectUtil.invoke(engine.getEngineConfig(), "getSharedObjectMap") as Map<String, Any>
        return map
    }
    fun addSharedObject(engine: Engine, kv:Kv){
        val ec=engine.getEngineConfig()
        try {
            kv.forEach { t, u -> ec.addSharedObject(t.toString(), u) }
        } catch (e: Exception) {
            println("""engine.addSharedObject failed ${e}""")
        }
    }

    /**
     * 将传入的所有列表中的元素清空
     * @param ArrayList 列表数组
     * @return 将传入的所有列表中的元素清空后返回
     */
    fun clearLists(vararg lists:ArrayList<*>):ArrayList<*>{
        lists.forEach {
            it.clear()
        }
        return arrayListOf(lists)
    }
}

enum class DbType {
    /**
     * The horizontal (right <-> left) orientation
     */
    HORIZONTAL,

    /**
     * The vertical (top <-> bottom) orientation
     */
    VERTICAL
}


class NowDirective : Directive() {
    override fun exec(env: Env?, scope: com.jfinal.template.stat.Scope?, writer: Writer?) {
        write(writer, Date().toString())
    }
}

class Demo : Directive() {
    // ExprList 代表指令参数表达式列表
    override fun setExprList(exprList: ExprList?) {
        // 在这里可以对 exprList 进行个性化控制
        super.setExprList(exprList)
    }

    override fun exec(env: Env?, scope: com.jfinal.template.stat.Scope?, writer: Writer?) {
        write(writer, "body 执行前\n")
        stat.exec(env, scope, writer)  // 执行 body
        write(writer, "body 执行后\n")
    }

    override fun hasEnd(): Boolean {
        return true  // 返回 true 则该指令拥有 #end 结束标记
    }
}